﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using org.in2bits.MyXls;
using System.Web;
using System.IO;

namespace HouseSecretary.Common
{
    public class ExcelClass
    {

        /// <summary>
        /// 导出excel通用方法(返回Byte[]类型)
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="excelTitle"></param>
        /// <param name="rowCount"></param>
        /// <param name="xlsxlsModelList"></param>
        public static void ExportToExcelReturnByte(string _mappath, string filename,string excelTitle, Model.ExcelTitleFont excelTitleFont, int rowCount, List<Model.XlsModel> xlsxlsModelList, List<Model.XlsModel_Column> xlsColList, List<Model.XlsModel_Row> xlsRowList, ref string saveFileName)
        {
            if (xlsxlsModelList.Count > 0)
            {
                int _tmpRowNum = xlsxlsModelList[0].RowIndex;
                int _colCount = 0;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    if (_xlsModel.RowIndex == _tmpRowNum)
                    {
                        _colCount += _xlsModel.ColSpan;
                    }
                }

                DataTable dt = new DataTable();

                for (int i = 0; i < _colCount; i++)
                {
                    dt.Columns.Add(i.ToString());

                }
                for (int i = 0; i < rowCount; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < _colCount; j++)
                    {
                        dr[j] = "0";
                    }
                    dt.Rows.Add(dr);

                }

                XlsDocument doc = new XlsDocument();
                doc.FileName = filename;
                Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
                //列设置
                if (xlsColList != null)
                {
                    foreach (Model.XlsModel_Column xlsCol in xlsColList)
                    {
                        ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
                        colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
                        colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
                        colInfo.Width = xlsCol.Width;
                        sheet.AddColumnInfo(colInfo);
                    }
                }
                //行设置
                if (xlsRowList != null)
                {
                    foreach (Model.XlsModel_Row xlsRow in xlsRowList)
                    {
                        RowInfo rol = new RowInfo();
                        rol.RowIndexStart = xlsRow.RowIndexStart;
                        rol.RowIndexEnd = xlsRow.RowIndexEnd;
                        rol.RowHeight = xlsRow.RowHeight;
                        sheet.AddRowInfo(rol);
                    }
                }

                //标题
                XF cellXF = doc.NewXF();//标题
                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                if (excelTitleFont != null)
                {
                    cellXF.Font.FontName = excelTitleFont.FontName;
                    cellXF.Font.Height = (ushort)excelTitleFont.FontSize;
                    cellXF.Font.Bold = excelTitleFont.Bold;
                }
                Cell cell = sheet.Cells.Add(1, 1, excelTitle, cellXF);
                MergeArea meaA = new MergeArea(1, 1, 1, _colCount);
                sheet.AddMergeArea(meaA);//填加合并单元格
                int _rownumber = 1;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    DataRow _dr = dt.Rows[_xlsModel.RowIndex - 1];
                    int _colindex = 1;
                    int _rowindex = _xlsModel.RowIndex;

                    for (int i = 0; i < _dr.ItemArray.Length; i++)
                    {
                        if (_dr.ItemArray[i].ToString() == "0")
                        {
                            _colindex = i + 1;
                            break;
                        }

                    }

                    cellXF = doc.NewXF();//内容
                    cellXF.TextWrapRight = true; //自动换行

                    switch (_xlsModel.VAlign)
                    {
                        case Model.XlsModel.VerticalAlign.NotSet: { cellXF.VerticalAlignment = VerticalAlignments.Centered; break; }
                        case Model.XlsModel.VerticalAlign.Bottom: { cellXF.VerticalAlignment = VerticalAlignments.Bottom; break; }
                        case Model.XlsModel.VerticalAlign.Middle: { cellXF.VerticalAlignment = VerticalAlignments.Centered; break; }
                        case Model.XlsModel.VerticalAlign.Top: { cellXF.VerticalAlignment = VerticalAlignments.Top; break; }

                    }

                    if (_xlsModel.UseBorder)
                    {
                        cellXF.UseBorder = true;
                        cellXF.TopLineStyle = _xlsModel.TopLineStyle;
                        cellXF.TopLineColor = Colors.Black;
                        cellXF.BottomLineStyle = _xlsModel.BottomLineStyle;
                        cellXF.BottomLineColor = Colors.Black;
                        cellXF.LeftLineStyle = _xlsModel.LeftLineStyle;
                        cellXF.LeftLineColor = Colors.Black;
                        cellXF.RightLineStyle = _xlsModel.RightLineStyle;
                        cellXF.RightLineColor = Colors.Black;
                    }

                    //单元格背景色
                    if (_xlsModel.UserPatternColor)
                    {
                        switch (_xlsModel.PColor)
                        {
                            case Model.XlsModel.PatternColor.Silver: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Silver; } break;
                            case Model.XlsModel.PatternColor.Yellow: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Yellow; } break;
                            case Model.XlsModel.PatternColor.Red: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Red; } break;
                            case Model.XlsModel.PatternColor.Blue: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Blue; } break;
                            case Model.XlsModel.PatternColor.Purple: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Purple; } break;
                            case Model.XlsModel.PatternColor.Grey: { cellXF.Pattern = 1; cellXF.PatternColor = Colors.Grey; } break;
                        }
                    }

                    switch (_xlsModel.HAlign)
                    {
                        case Model.XlsModel.HorizontalAlign.Left:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Left;//设定文字居左
                                break;
                            }
                        case Model.XlsModel.HorizontalAlign.Center:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                                break;
                            }
                        case Model.XlsModel.HorizontalAlign.Right:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Right;//设定文字居右
                                break;
                            }

                    }
                    switch (_xlsModel.TitleType)
                    {
                        case Model.XlsModel.ContentType.String:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);

                                break;
                            }
                        case Model.XlsModel.ContentType.Number:
                            {
                                
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, Convert.ToDouble(_xlsModel.Title), cellXF);
                                break;
                            }
                        default:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);

                                break;
                            }
                    }

                    dt.Rows[_rowindex - 1][_colindex - 1] = "1";

                    if (_xlsModel.RowSpan > 1)
                    {
                        if (_xlsModel.ColSpan > 1)
                        {
                            MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _xlsModel.ColSpan + _colindex - 1, _xlsModel.TitleType);
                            for (int i = 1; i < _xlsModel.ColSpan; i++)
                            {
                                dt.Rows[_rowindex - 1][_colindex - 1 + i] = "1";
                            }
                            for (int i = 1; i < _xlsModel.RowSpan; i++)
                            {
                                dt.Rows[_rowindex - 1 + i][_colindex] = "1";
                            }
                        }
                        else
                        {
                            MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _colindex, _xlsModel.TitleType);
                        }
                        //meaA = new MergeArea(_rowindex + _rownumber, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _colindex, _colindex);
                        //sheet.AddMergeArea(meaA);//填加合并单元格
                        for (int i = 1; i < _xlsModel.RowSpan; i++)
                        {
                            // dt.Rows[_rowindex - 1 + i][_colindex - 1] = "1";
                            for (int j = 0; j < _xlsModel.ColSpan; j++)
                            {
                                dt.Rows[_rowindex - 1 + i][_colindex - 1 + j] = "1";
                            }
                        }

                    }

                    if (_xlsModel.RowSpan == 1)
                    {
                        if (_xlsModel.ColSpan > 1)
                        {

                            MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber, _colindex + _xlsModel.ColSpan - 1, _xlsModel.TitleType);
                            //meaA = new MergeArea(_rowindex + _rownumber, _rowindex + _rownumber, _colindex, _colindex + _xlsModel.ColSpan - 1);
                            //sheet.AddMergeArea(meaA);//填加合并单元格
                            for (int i = 1; i < _xlsModel.ColSpan; i++)
                            {
                                dt.Rows[_rowindex - 1][_colindex - 1 + i] = "1";

                            }


                        }
                    }


                }
                doc.Save(_mappath, true);
                saveFileName = _mappath + @"\" + doc.FileName;

            }
            else
            {
                saveFileName = "";
            }
        }




        /// <summary>
        /// 导出excel通用方法
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="excelTitle"></param>
        /// <param name="rowCount"></param>
        /// <param name="xlsxlsModelList"></param>
        public static void ExportToExcelReturnPath(string filename, string excelTitle, int rowCount, List<Model.XlsModel> xlsxlsModelList, List<Model.XlsModel_Column> xlsColList, List<Model.XlsModel_Row> xlsRowList, ref string saveFileName)
        {
            //string _filepath = "";
            filename = filename.Replace(".xls", "");
            filename += Guid.NewGuid();
            if (xlsxlsModelList.Count > 0)
            {
                int _tmpRowNum = xlsxlsModelList[0].RowIndex;
                int _colCount = 0;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    if (_xlsModel.RowIndex == _tmpRowNum)
                    {
                        _colCount += _xlsModel.ColSpan;
                    }
                }

                DataTable dt = new DataTable();

                for (int i = 0; i < _colCount; i++)
                {
                    dt.Columns.Add(i.ToString());

                }
                for (int i = 0; i < rowCount; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < _colCount; j++)
                    {
                        dr[j] = "0";
                    }
                    dt.Rows.Add(dr);

                }


                XlsDocument doc = new XlsDocument();
                doc.FileName = filename;
                Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
                //列设置
                if (xlsColList != null)
                {
                    foreach (Model.XlsModel_Column xlsCol in xlsColList)
                    {
                        ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
                        colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
                        colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
                        colInfo.Width = xlsCol.Width;
                        sheet.AddColumnInfo(colInfo);
                    }
                }
                //行设置
                if (xlsRowList != null)
                {
                    foreach (Model.XlsModel_Row xlsRow in xlsRowList)
                    {
                        RowInfo rol = new RowInfo();
                        rol.RowIndexStart = xlsRow.RowIndexStart;
                        rol.RowIndexEnd = xlsRow.RowIndexEnd;
                        rol.RowHeight = xlsRow.RowHeight;
                        sheet.AddRowInfo(rol);
                    }
                }

                XF cellXF = doc.NewXF();//标题
                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                Cell cell = sheet.Cells.Add(1, 1, excelTitle, cellXF);
                MergeArea meaA = new MergeArea(1, 1, 1, _colCount);
                sheet.AddMergeArea(meaA);//填加合并单元格

                int _rownumber = 1;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    DataRow _dr = dt.Rows[_xlsModel.RowIndex - 1];
                    int _colindex = 1;
                    int _rowindex = _xlsModel.RowIndex;
                    for (int i = 0; i < _dr.ItemArray.Length; i++)
                    {
                        if (_dr.ItemArray[i].ToString() == "0")
                        {
                            _colindex = i + 1;
                            break;
                        }

                    }

                    cellXF = doc.NewXF();//内容
                    cellXF.TextWrapRight = true; //自动换行
                    cellXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中  
                    cellXF.UseBorder = true;
                    cellXF.TopLineStyle = 1;
                    cellXF.TopLineColor = Colors.Black;
                    cellXF.BottomLineStyle = 1;
                    cellXF.BottomLineColor = Colors.Black;
                    cellXF.LeftLineStyle = 1;
                    cellXF.LeftLineColor = Colors.Black;
                    cellXF.RightLineStyle = 1;
                    cellXF.RightLineColor = Colors.Black;

                    switch (_xlsModel.HAlign)
                    {
                        case Model.XlsModel.HorizontalAlign.Left:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Left;//设定文字居左
                                break;
                            }
                        case Model.XlsModel.HorizontalAlign.Center:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                                break;
                            }
                        case Model.XlsModel.HorizontalAlign.Right:
                            {
                                cellXF.HorizontalAlignment = HorizontalAlignments.Right;//设定文字居右
                                break;
                            }

                    }

                    switch (_xlsModel.TitleType)
                    {
                        case Model.XlsModel.ContentType.String:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);
                                break;
                            }
                        case Model.XlsModel.ContentType.Number:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, Convert.ToDouble(_xlsModel.Title), cellXF);
                                break;
                            }
                        default:
                            {
                                cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);
                                break;
                            }
                    }
                    dt.Rows[_rowindex - 1][_colindex - 1] = "1";

                    if (_xlsModel.RowSpan > 1)
                    {
                        if (_xlsModel.ColSpan > 1)
                        {
                            MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _xlsModel.ColSpan + _colindex - 1, _xlsModel.TitleType);
                            for (int i = 1; i < _xlsModel.ColSpan; i++)
                            {
                                dt.Rows[_rowindex - 1][_colindex - 1 + i] = "1";
                            }
                            for (int i = 1; i < _xlsModel.RowSpan; i++)
                            {
                                dt.Rows[_rowindex - 1 + i][_colindex] = "1";
                            }
                        }
                        else
                        {
                            MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _colindex, _xlsModel.TitleType);
                        }
                        //meaA = new MergeArea(_rowindex + _rownumber, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _colindex, _colindex);
                        //sheet.AddMergeArea(meaA);//填加合并单元格
                        for (int i = 1; i < _xlsModel.RowSpan; i++)
                        {
                            dt.Rows[_rowindex - 1 + i][_colindex - 1] = "1";
                        }

                    }
                    if (_xlsModel.RowSpan == 1)
                    {
                        if (_xlsModel.ColSpan > 1)
                        {

                            MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber, _colindex + _xlsModel.ColSpan - 1, _xlsModel.TitleType);
                            //meaA = new MergeArea(_rowindex + _rownumber, _rowindex + _rownumber, _colindex, _colindex + _xlsModel.ColSpan - 1);
                            //sheet.AddMergeArea(meaA);//填加合并单元格
                            for (int i = 1; i < _xlsModel.ColSpan; i++)
                            {
                                dt.Rows[_rowindex - 1][_colindex - 1 + i] = "1";

                            }


                        }
                    }

                }

                //doc.Send();
                Addfolder("~/tempfile");

                string _mappath = HttpContext.Current.Server.MapPath("~/tempfile");
                doc.Save(_mappath, true);
                saveFileName = "/tempfile/" + doc.FileName;
                //doc.Send();
                //HttpContext.Current.Response.Flush();
                //HttpContext.Current.Response.End();

            }
        }


        /// <summary>
        /// 导出excel通用方法(分页)
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="excelTitle"></param>
        /// <param name="rowCount"></param>
        /// <param name="xlsxlsModelList"></param>
        //public static Byte[] ExportToExcelReturnPathPage(string filename, string excelTitle, int rowCount, List<Model.XlsModel> xlsxlsModelList, List<Model.XlsModel_Column> xlsColList, List<Model.XlsModel_Row> xlsRowList)
        //{
        //    filename = filename.Replace(".xls", "");
        //    filename += Guid.NewGuid();
        //    if (xlsxlsModelList.Count > 0)
        //    {
        //        int _tmpRowNum = xlsxlsModelList[0].RowIndex;
        //        int _colCount = 0;
        //        foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
        //        {
        //            if (_xlsModel.RowIndex == _tmpRowNum)
        //            {
        //                _colCount += _xlsModel.ColSpan;
        //            }
        //        }

        //        DataTable dt = new DataTable();

        //        for (int i = 0; i < _colCount; i++)
        //        {
        //            dt.Columns.Add(i.ToString());

        //        }
        //        for (int i = 0; i < rowCount; i++)
        //        {
        //            DataRow dr = dt.NewRow();
        //            for (int j = 0; j < _colCount; j++)
        //            {
        //                dr[j] = "0";
        //            }
        //            dt.Rows.Add(dr);

        //        }


        //        XlsDocument doc = new XlsDocument();
        //        doc.FileName = filename;
        //        Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
        //        //列设置
        //        if (xlsColList != null)
        //        {
        //            foreach (Model.XlsModel_Column xlsCol in xlsColList)
        //            {
        //                ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
        //                colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
        //                colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
        //                colInfo.Width = xlsCol.Width;
        //                sheet.AddColumnInfo(colInfo);
        //            }
        //        }
        //        //行设置
        //        if (xlsRowList != null)
        //        {
        //            foreach (Model.XlsModel_Row xlsRow in xlsRowList)
        //            {
        //                RowInfo rol = new RowInfo();
        //                rol.RowIndexStart = xlsRow.RowIndexStart;
        //                rol.RowIndexEnd = xlsRow.RowIndexEnd;
        //                rol.RowHeight = xlsRow.RowHeight;
        //                sheet.AddRowInfo(rol);
        //            }
        //        }

        //        XF cellXF = doc.NewXF();//标题
        //        cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
        //        Cell cell = sheet.Cells.Add(1, 1, excelTitle, cellXF);
        //        MergeArea meaA = new MergeArea(1, 1, 1, _colCount);
        //        sheet.AddMergeArea(meaA);//填加合并单元格

        //        bool bl = false;//分页
        //        int dtIndex = 0;
        //        int pagSize = 65000;
        //        int pageIndex = 1;
        //        int sheetIndex = 1;
        //        int DValue = 0;
        //        foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
        //        {
        //            int _rowindex = _xlsModel.RowIndex;
        //            dtIndex = _rowindex;
        //            if (_rowindex == pagSize)//分页
        //            {
        //                pagSize = pagSize / pageIndex * (++pageIndex);
        //                sheet = doc.Workbook.Worksheets.Add("sheet" + (++sheetIndex));
        //                //列设置
        //                if (xlsColList != null)
        //                {
        //                    foreach (Model.XlsModel_Column xlsCol in xlsColList)
        //                    {
        //                        ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
        //                        colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
        //                        colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
        //                        colInfo.Width = xlsCol.Width;
        //                        sheet.AddColumnInfo(colInfo);
        //                    }
        //                }
        //                //行设置
        //                if (xlsRowList != null)
        //                {
        //                    foreach (Model.XlsModel_Row xlsRow in xlsRowList)
        //                    {
        //                        RowInfo rol = new RowInfo();
        //                        rol.RowIndexStart = xlsRow.RowIndexStart;
        //                        rol.RowIndexEnd = xlsRow.RowIndexEnd;
        //                        rol.RowHeight = xlsRow.RowHeight;
        //                        sheet.AddRowInfo(rol);
        //                    }
        //                }
        //                sheet.Cells.Add(1, 1, excelTitle, cellXF);
        //                sheet.AddMergeArea(meaA);//填加合并单元格

        //                //dt.Rows[0][0] = "0";
        //                //dt.Rows[0][1] = "0";
        //                //dt.Rows[0][2] = "0";
        //                //dt.Rows[0][3] = "0";
        //                //dt.Rows[0][4] = "0";
        //                for (int i = 0; i < _colCount; i++)
        //                {
        //                    SetRowCell(dt, xlsxlsModelList[i], 1, 1, sheet, cellXF, doc, cell);
        //                }

        //                bl = true;

        //                DValue = pagSize / pageIndex * (pageIndex - 1) - 2;
        //            }

        //            if (bl)
        //            {
        //                _rowindex = _rowindex - DValue;
        //            }

        //            SetRowCell(dt, _xlsModel, _rowindex, dtIndex, sheet, cellXF, doc, cell);

        //        }

        //        //doc.Send();
        //        //HttpContext.Current.Response.Flush();
        //        //HttpContext.Current.Response.End();
        //        return doc.Bytes.ByteArray;

        //    }
        //    else
        //    {
        //        return null;
        //    }
        //}

        /// <summary>
        /// 导出excel通用方法(分页)
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="excelTitle"></param>
        /// <param name="rowCount"></param>
        /// <param name="xlsxlsModelList"></param>
        public static void ExportToExcelReturnPathPage(string filename, string excelTitle, int rowCount, List<Model.XlsModel> xlsxlsModelList, List<Model.XlsModel_Column> xlsColList, List<Model.XlsModel_Row> xlsRowList)
        {
            filename = filename.Replace(".xls", "");
            filename += Guid.NewGuid();
            if (xlsxlsModelList.Count > 0)
            {
                int _tmpRowNum = xlsxlsModelList[0].RowIndex;
                int _colCount = 0;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    if (_xlsModel.RowIndex == _tmpRowNum)
                    {
                        _colCount += _xlsModel.ColSpan;
                    }
                }

                DataTable dt = new DataTable();

                for (int i = 0; i < _colCount; i++)
                {
                    dt.Columns.Add(i.ToString());

                }
                for (int i = 0; i < rowCount; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < _colCount; j++)
                    {
                        dr[j] = "0";
                    }
                    dt.Rows.Add(dr);

                }


                XlsDocument doc = new XlsDocument();
                doc.FileName = filename;
                Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
                //列设置
                if (xlsColList != null)
                {
                    foreach (Model.XlsModel_Column xlsCol in xlsColList)
                    {
                        ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
                        colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
                        colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
                        colInfo.Width = xlsCol.Width;
                        sheet.AddColumnInfo(colInfo);
                    }
                }
                //行设置
                if (xlsRowList != null)
                {
                    foreach (Model.XlsModel_Row xlsRow in xlsRowList)
                    {
                        RowInfo rol = new RowInfo();
                        rol.RowIndexStart = xlsRow.RowIndexStart;
                        rol.RowIndexEnd = xlsRow.RowIndexEnd;
                        rol.RowHeight = xlsRow.RowHeight;
                        sheet.AddRowInfo(rol);
                    }
                }

                XF cellXF = doc.NewXF();//标题
                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                Cell cell = sheet.Cells.Add(1, 1, excelTitle, cellXF);
                MergeArea meaA = new MergeArea(1, 1, 1, _colCount);
                sheet.AddMergeArea(meaA);//填加合并单元格

                bool bl = false;//分页
                int dtIndex = 0;
                int pagSize = 65000;
                int pageIndex = 1;
                int sheetIndex = 1;
                int DValue = 0;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    int _rowindex = _xlsModel.RowIndex;
                    dtIndex = _rowindex;
                    if (_rowindex == pagSize)//分页
                    {
                        pagSize = pagSize / pageIndex * (++pageIndex);
                        sheet = doc.Workbook.Worksheets.Add("sheet" + (++sheetIndex));
                        //列设置
                        if (xlsColList != null)
                        {
                            foreach (Model.XlsModel_Column xlsCol in xlsColList)
                            {
                                ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
                                colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
                                colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
                                colInfo.Width = xlsCol.Width;
                                sheet.AddColumnInfo(colInfo);
                            }
                        }
                        //行设置
                        if (xlsRowList != null)
                        {
                            foreach (Model.XlsModel_Row xlsRow in xlsRowList)
                            {
                                RowInfo rol = new RowInfo();
                                rol.RowIndexStart = xlsRow.RowIndexStart;
                                rol.RowIndexEnd = xlsRow.RowIndexEnd;
                                rol.RowHeight = xlsRow.RowHeight;
                                sheet.AddRowInfo(rol);
                            }
                        }
                        sheet.Cells.Add(1, 1, excelTitle, cellXF);
                        sheet.AddMergeArea(meaA);//填加合并单元格

                        //dt.Rows[0][0] = "0";
                        //dt.Rows[0][1] = "0";
                        //dt.Rows[0][2] = "0";
                        //dt.Rows[0][3] = "0";
                        //dt.Rows[0][4] = "0";
                        for (int i = 0; i < _colCount; i++)
                        {
                            SetRowCell(dt, xlsxlsModelList[i], 1, 1, sheet, cellXF, doc, cell);
                        }

                        bl = true;

                        DValue = pagSize / pageIndex * (pageIndex - 1) - 2;
                    }

                    if (bl)
                    {
                        _rowindex = _rowindex - DValue;
                    }

                    SetRowCell(dt, _xlsModel, _rowindex, dtIndex, sheet, cellXF, doc, cell);

                }

                doc.Send();
                HttpContext.Current.Response.Flush();
                HttpContext.Current.Response.End();

            }
        }

        /// <summary>
        /// 导出excel通用方法(分页)
        /// </summary>
        /// <param name="filename"></param>
        /// <param name="excelTitle"></param>
        /// <param name="rowCount"></param>
        /// <param name="xlsxlsModelList"></param>
        /// <param name="xlsColList"></param>
        /// <param name="xlsRowList"></param>
        /// <param name="path">导出excel保存文件名</param>
        public static void ExportToExcelReturnPathPage(string filename, string excelTitle, int rowCount, List<Model.XlsModel> xlsxlsModelList, List<Model.XlsModel_Column> xlsColList, List<Model.XlsModel_Row> xlsRowList, string path, ref string saveFileName)
        {
            filename = filename.Replace(".xls", "");
            filename += Guid.NewGuid();
            if (xlsxlsModelList.Count > 0)
            {
                int _tmpRowNum = xlsxlsModelList[0].RowIndex;
                int _colCount = 0;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    if (_xlsModel.RowIndex == _tmpRowNum)
                    {
                        _colCount += _xlsModel.ColSpan;
                    }
                }

                DataTable dt = new DataTable();

                for (int i = 0; i < _colCount; i++)
                {
                    dt.Columns.Add(i.ToString());

                }
                for (int i = 0; i < rowCount; i++)
                {
                    DataRow dr = dt.NewRow();
                    for (int j = 0; j < _colCount; j++)
                    {
                        dr[j] = "0";
                    }
                    dt.Rows.Add(dr);

                }


                XlsDocument doc = new XlsDocument();
                doc.FileName = filename;
                Worksheet sheet = doc.Workbook.Worksheets.Add("sheet1");
                //列设置
                if (xlsColList != null)
                {
                    foreach (Model.XlsModel_Column xlsCol in xlsColList)
                    {
                        ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
                        colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
                        colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
                        colInfo.Width = xlsCol.Width;
                        sheet.AddColumnInfo(colInfo);
                    }
                }
                //行设置
                if (xlsRowList != null)
                {
                    foreach (Model.XlsModel_Row xlsRow in xlsRowList)
                    {
                        RowInfo rol = new RowInfo();
                        rol.RowIndexStart = xlsRow.RowIndexStart;
                        rol.RowIndexEnd = xlsRow.RowIndexEnd;
                        rol.RowHeight = xlsRow.RowHeight;
                        sheet.AddRowInfo(rol);
                    }
                }

                XF cellXF = doc.NewXF();//标题
                cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                Cell cell = sheet.Cells.Add(1, 1, excelTitle, cellXF);
                MergeArea meaA = new MergeArea(1, 1, 1, _colCount);
                sheet.AddMergeArea(meaA);//填加合并单元格

                bool bl = false;//分页
                int dtIndex = 0;
                int pagSize = 65000;
                int pageIndex = 1;
                int sheetIndex = 1;
                int DValue = 0;
                foreach (Model.XlsModel _xlsModel in xlsxlsModelList)
                {
                    int _rowindex = _xlsModel.RowIndex;
                    dtIndex = _rowindex;
                    if (_rowindex == pagSize)//分页
                    {
                        pagSize = pagSize / pageIndex * (++pageIndex);
                        sheet = doc.Workbook.Worksheets.Add("sheet" + (++sheetIndex));
                        //列设置
                        if (xlsColList != null)
                        {
                            foreach (Model.XlsModel_Column xlsCol in xlsColList)
                            {
                                ColumnInfo colInfo = new ColumnInfo(doc, sheet);//生成列格式对象
                                colInfo.ColumnIndexStart = xlsCol.ColumnIndexStart;
                                colInfo.ColumnIndexEnd = xlsCol.ColumnIndexEnd;
                                colInfo.Width = xlsCol.Width;
                                sheet.AddColumnInfo(colInfo);
                            }
                        }
                        //行设置
                        if (xlsRowList != null)
                        {
                            foreach (Model.XlsModel_Row xlsRow in xlsRowList)
                            {
                                RowInfo rol = new RowInfo();
                                rol.RowIndexStart = xlsRow.RowIndexStart;
                                rol.RowIndexEnd = xlsRow.RowIndexEnd;
                                rol.RowHeight = xlsRow.RowHeight;
                                sheet.AddRowInfo(rol);
                            }
                        }
                        sheet.Cells.Add(1, 1, excelTitle, cellXF);
                        sheet.AddMergeArea(meaA);//填加合并单元格

                        //dt.Rows[0][0] = "0";
                        //dt.Rows[0][1] = "0";
                        //dt.Rows[0][2] = "0";
                        //dt.Rows[0][3] = "0";
                        //dt.Rows[0][4] = "0";
                        for (int i = 0; i < _colCount; i++)
                        {
                            SetRowCell(dt, xlsxlsModelList[i], 1, 1, sheet, cellXF, doc, cell);
                        }

                        bl = true;

                        DValue = pagSize / pageIndex * (pageIndex - 1) - 2;
                    }

                    if (bl)
                    {
                        _rowindex = _rowindex - DValue;
                    }

                    SetRowCell(dt, _xlsModel, _rowindex, dtIndex, sheet, cellXF, doc, cell);

                }
                saveFileName = doc.FileName;
                doc.Save(path);


            }
        }

        //设置EXCEL数据
        public static void SetRowCell(DataTable dt, Model.XlsModel _xlsModel, int _rowindex, int dtIndex, Worksheet sheet, XF cellXF, XlsDocument doc, Cell cell)
        {
            int _rownumber = 1;

            DataRow _dr = dt.Rows[_xlsModel.RowIndex - 1];
            int _colindex = 1;

            for (int i = 0; i < _dr.ItemArray.Length; i++)
            {
                if (_dr.ItemArray[i].ToString() == "0")
                {
                    _colindex = i + 1;
                    break;
                }

            }

            cellXF = doc.NewXF();//内容
            cellXF.TextWrapRight = true; //自动换行
            cellXF.VerticalAlignment = VerticalAlignments.Centered; // 垂直居中  
            cellXF.UseBorder = true;
            cellXF.TopLineStyle = 1;
            cellXF.TopLineColor = Colors.Black;
            cellXF.BottomLineStyle = 1;
            cellXF.BottomLineColor = Colors.Black;
            cellXF.LeftLineStyle = 1;
            cellXF.LeftLineColor = Colors.Black;
            cellXF.RightLineStyle = 1;
            cellXF.RightLineColor = Colors.Black;

            switch (_xlsModel.HAlign)
            {
                case Model.XlsModel.HorizontalAlign.Left:
                    {
                        cellXF.HorizontalAlignment = HorizontalAlignments.Left;//设定文字居左
                        break;
                    }
                case Model.XlsModel.HorizontalAlign.Center:
                    {
                        cellXF.HorizontalAlignment = HorizontalAlignments.Centered;//设定文字居中
                        break;
                    }
                case Model.XlsModel.HorizontalAlign.Right:
                    {
                        cellXF.HorizontalAlignment = HorizontalAlignments.Right;//设定文字居右
                        break;
                    }

            }

            switch (_xlsModel.TitleType)
            {
                case Model.XlsModel.ContentType.String:
                    {
                        cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);
                        break;
                    }
                case Model.XlsModel.ContentType.Number:
                    {
                        cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, Convert.ToDouble(_xlsModel.Title), cellXF);
                        break;
                    }
                default:
                    {
                        cell = sheet.Cells.Add(_rowindex + _rownumber, _colindex, _xlsModel.Title, cellXF);
                        break;
                    }
            }
            dt.Rows[dtIndex - 1][_colindex - 1] = "1";

            if (_xlsModel.RowSpan > 1)
            {
                if (_xlsModel.ColSpan > 1)
                {
                    MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _xlsModel.ColSpan + _colindex - 1, _xlsModel.TitleType);
                    for (int i = 1; i < _xlsModel.ColSpan; i++)
                    {
                        dt.Rows[_rowindex - 1][_colindex - 1 + i] = "1";
                    }
                    for (int i = 1; i < _xlsModel.RowSpan; i++)
                    {
                        dt.Rows[_rowindex - 1 + i][_colindex] = "1";
                    }
                }
                else
                {
                    MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber + _xlsModel.RowSpan - 1, _colindex, _xlsModel.TitleType);
                }
                for (int i = 1; i < _xlsModel.RowSpan; i++)
                {
                    dt.Rows[dtIndex - 1 + i][_colindex - 1] = "1";
                }

            }
            if (_xlsModel.RowSpan == 1)
            {
                if (_xlsModel.ColSpan > 1)
                {

                    MergeRegion(ref sheet, cellXF, _xlsModel.Title, _rowindex + _rownumber, _colindex, _rowindex + _rownumber, _colindex + _xlsModel.ColSpan - 1, _xlsModel.TitleType);
                    for (int i = 1; i < _xlsModel.ColSpan; i++)
                    {
                        dt.Rows[dtIndex - 1][_colindex - 1 + i] = "1";

                    }

                }
            }
        }



        /// <summary>
        /// 合并行列的方法
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="xf"></param>
        /// <param name="title"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        private static void MergeRegion(ref Worksheet ws, XF xf, string title, int startRow, int startCol, int endRow, int endCol,Model.XlsModel.ContentType ModelType)
        {
            for (int i = startCol; i <= endCol; i++)
            {
                for (int j = startRow; j <= endRow; j++)
                {
                    if (ModelType == Model.XlsModel.ContentType.Number)
                    {

                        ws.Cells.Add(j, i, Convert.ToDouble(title), xf);
                    }
                    else
                    {
                        ws.Cells.Add(j, i, title, xf);
                    }
                }
            }
            ws.Cells.Merge(startRow, endRow, startCol, endCol);
        }

        /// <summary>
        /// 创建文件夹
        /// </summary>
        /// <param name="foldername">相对路径</param>
        private static void Addfolder(string foldername)
        {
            //获取文件夹绝对路径
            try
            {
                string url = HttpContext.Current.Server.MapPath(foldername);
                //如果不存在该文件夹就创建
                if (!Directory.Exists(url))
                {
                    Directory.CreateDirectory(url);
                }
            }
            catch (Exception)
            {

            }
        }


    }
}
